﻿CREATE PROCEDURE [dbo].[Text_BookTree_Get]
	@userId UNIQUEIDENTIFIER = NULL,
	@url NVARCHAR(MAX),
	@fullDeep BIT = 0
AS
BEGIN
	SET @url = LOWER(@url)

	DECLARE @bookId UNIQUEIDENTIFIER
	DECLARE @leftKey BIGINT
	DECLARE @rightKey BIGINT
	DECLARE @level TINYINT
	DECLARE @id INT

	SELECT @id = bt.[BookTreeId]
				,@leftKey = bt.[LeftKey]
				,@rightKey = bt.[RightKey]
				,@level = bt.[Level]
				,@bookId = bt.[BookId]
	FROM [dbo].[text_BookTree] AS bt
	INNER JOIN [dbo].[text_BookTree_Cache] AS c ON c.BookTreeId = bt.BookTreeId
	WHERE c.[Url] = @url

	IF @id IS NULL
	BEGIN
		RAISERROR ('UnknownUrl', 16, 1)
		RETURN
	END


	SELECT [Author]
				,[CoverUrl]
				,CASE WHEN EXISTS (SELECT * FROM [dbo].[ebook_Item] WHERE [BookId] = @bookId AND [Enable] = 1) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [ExistPdf]
	FROM [dbo].[text_Book]
	WHERE [BookId] = @bookId

	SELECT DISTINCT bt.[BookTreeId]
			,bt.[ParentBookTreeId]
			,bt.[LeftKey]
			--,bt.[RightKey]
			,bt.[Level]
			--,bt.[BookId]
			,bt.[HashTag]
			,bt.[IsHead]
			,bt.[Text]
			,bt.[Published]
			--,bt.[UpdateDate]
				,c.[Url]
	FROM [dbo].[text_BookTree] AS bt
	LEFT OUTER JOIN [dbo].[text_BookTree_Cache] AS c ON bt.[BookTreeId] = c.[BookTreeId]
	WHERE bt.[BookId] = @bookId AND ((bt.[RightKey] > @leftKey AND bt.[LeftKey] < @rightKey AND (@fullDeep = 1 OR bt.[Level] <= @level + 1)) OR [IsHead] = 1)
END